/*
 * @author Kyle Kemp
 */
package edu.cs346.group5.backend;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * The Class Database.
 */
public class Database {

	/** The Constant driver. */
	private final static String driver = "com.mysql.jdbc.Driver";

	/** The Constant database. */
	private final static String database = "cs341g5";
	
	private final static String user = database;
	private final static String pass = "d632449";

	/** The connection. */
	private static Connection conn;

	/**
	 * Connect.
	 * 
	 * @return the connection
	 * @throws SQLException
	 *             the sQL exception
	 */
	private static Connection connect() throws SQLException {
		try {
			Class.forName(driver).newInstance();
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection rv = DriverManager.getConnection("jdbc:mysql://cs341.cs.uwosh.edu:4381/" + database, user, pass);
		rv.setAutoCommit(true);
		return rv;
	}

	/**
	 * Execute a raw query. Use this for updating and deleting.
	 * 
	 * @param query
	 *            the query
	 * @throws SQLException
	 *             the sQL exception
	 */
	public static void execRaw(String query) throws SQLException {
		if (conn == null)
			conn = connect();
		Statement stmt = conn.createStatement();
		stmt.execute(query);
		stmt.close();
	}

	/**
	 * Insert a row into the database.
	 * 
	 * @param table
	 *            the table
	 * @param columns
	 *            the columns
	 * @param values
	 *            the values
	 * @throws SQLException
	 *             the sQL exception
	 */
	public static void insert(String table, String columns, String values)
			throws SQLException {
		if (conn == null)
			conn = connect();
		Statement stmt = conn.createStatement();
		stmt.execute("insert into " + table + "(" + columns + ") values("
				+ values + ")");
		stmt.close();
	}

	/**
	 * Select from the database.
	 * 
	 * @param query
	 *            the query
	 * @param max
	 *            the max
	 * @return the list
	 * @throws SQLException
	 *             the sQL exception
	 */
	public static List<HashMap<String, Object>> select(String query, int max)
			throws SQLException {
		if (conn == null)
			conn = connect();
		Statement stmt = conn.createStatement();
		stmt.setMaxRows(max);
		List<HashMap<String, Object>> rv = convertResultSetToList(stmt
				.executeQuery(query));
		stmt.close();
		return rv;
	}

	/**
	 * Select using a query.
	 * 
	 * @param query
	 *            the query
	 * @return the list
	 * @throws SQLException
	 *             the sQL exception
	 */
	public static List<HashMap<String, Object>> select(String query)
			throws SQLException {
		return select(query, 0);
	}

	/**
	 * Checks if there exists a row in the query.
	 * 
	 * @param query
	 *            the query
	 * @return true, if successful
	 * @throws SQLException
	 *             the sQL exception
	 */
	public static boolean hasRow(String query) throws SQLException {
		return select(query).size() > 0;
	}

	// turn a result set into an iterable, mutable list
	/**
	 * Convert result set to list.
	 * 
	 * @param rs
	 *            the rs
	 * @return the list
	 * @throws SQLException
	 *             the sQL exception
	 */
	private static List<HashMap<String, Object>> convertResultSetToList(
			ResultSet rs) throws SQLException {
		ResultSetMetaData md = rs.getMetaData();
		int columns = md.getColumnCount();
		List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();

		while (rs.next()) {
			HashMap<String, Object> row = new HashMap<String, Object>(columns);
			for (int i = 1; i <= columns; ++i) {
				row.put(md.getColumnName(i), rs.getObject(i));
			}
			list.add(row);
		}

		return list;
	}
}